"""add aic to acme certificates

Revision ID: 612b6c93f9a9
Revises: 41b8ee38619d
Create Date: 2025-10-16 16:32:10.772077

"""

from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
import sqlmodel


# revision identifiers, used by Alembic.
revision: str = "612b6c93f9a9"
down_revision: Union[str, None] = "41b8ee38619d"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    revocation_enum = sa.Enum(
        "UNSPECIFIED",
        "KEY_COMPROMISE",
        "CA_COMPROMISE",
        "AFFILIATION_CHANGED",
        "SUPERSEDED",
        "CESSATION_OF_OPERATION",
        name="revocationreason",
    )
    op.add_column(
        "acme_certificates",
        sa.Column("aic", sqlmodel.sql.sqltypes.AutoString(length=255), nullable=True),
    )
    op.alter_column(
        "acme_certificates",
        "revocation_reason",
        existing_type=sa.INTEGER(),
        type_=revocation_enum,
        postgresql_using=(
            "CASE "
            "WHEN revocation_reason IS NULL THEN NULL "
            "WHEN revocation_reason = 0 THEN 'UNSPECIFIED' "
            "WHEN revocation_reason = 1 THEN 'KEY_COMPROMISE' "
            "WHEN revocation_reason = 2 THEN 'CA_COMPROMISE' "
            "WHEN revocation_reason = 3 THEN 'AFFILIATION_CHANGED' "
            "WHEN revocation_reason = 4 THEN 'SUPERSEDED' "
            "WHEN revocation_reason = 5 THEN 'CESSATION_OF_OPERATION' "
            "ELSE 'UNSPECIFIED' "
            "END::revocationreason"
        ),
        existing_nullable=True,
    )
    op.create_index(
        op.f("ix_acme_certificates_aic"), "acme_certificates", ["aic"], unique=False
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    revocation_enum = sa.Enum(
        "UNSPECIFIED",
        "KEY_COMPROMISE",
        "CA_COMPROMISE",
        "AFFILIATION_CHANGED",
        "SUPERSEDED",
        "CESSATION_OF_OPERATION",
        name="revocationreason",
    )
    op.alter_column(
        "certificates",
        "revocation_reason",
        existing_type=revocation_enum,
        type_=sa.VARCHAR(),
        postgresql_using=(
            "CASE "
            "WHEN revocation_reason IS NULL THEN NULL "
            "WHEN revocation_reason = 'KEY_COMPROMISE' THEN 'keyCompromise' "
            "WHEN revocation_reason = 'CA_COMPROMISE' THEN 'caCompromise' "
            "WHEN revocation_reason = 'AFFILIATION_CHANGED' THEN 'affiliationChanged' "
            "WHEN revocation_reason = 'SUPERSEDED' THEN 'superseded' "
            "WHEN revocation_reason = 'CESSATION_OF_OPERATION' THEN 'cessationOfOperation' "
            "ELSE 'unspecified' "
            "END"
        ),
        existing_nullable=True,
    )
    op.drop_index(op.f("ix_acme_certificates_aic"), table_name="acme_certificates")
    op.alter_column(
        "acme_certificates",
        "revocation_reason",
        existing_type=revocation_enum,
        type_=sa.INTEGER(),
        postgresql_using=(
            "CASE "
            "WHEN revocation_reason IS NULL THEN NULL "
            "WHEN revocation_reason = 'UNSPECIFIED' THEN 0 "
            "WHEN revocation_reason = 'KEY_COMPROMISE' THEN 1 "
            "WHEN revocation_reason = 'CA_COMPROMISE' THEN 2 "
            "WHEN revocation_reason = 'AFFILIATION_CHANGED' THEN 3 "
            "WHEN revocation_reason = 'SUPERSEDED' THEN 4 "
            "WHEN revocation_reason = 'CESSATION_OF_OPERATION' THEN 5 "
            "ELSE 0 "
            "END"
        ),
        existing_nullable=True,
    )
    op.drop_column("acme_certificates", "aic")
    # ### end Alembic commands ###
